Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# CATEGORIZE

The CATEGORIZE function groups the values of a numeric expression into categories. You define the categories by specifying a series of increasing numeric values. The result that CATEGORIZE returns is dimensioned by all the dimensions of expression. For each cell in expression, CATEGORIZE returns one of the following: the category in which the number falls, zero (`0`) for a value below the range of the first category, minus one (`-1`) for a value above the range of the last category, or `NA` for an `NA` value.

Return Value

DECIMAL

Syntax

CATEGORIZE(expression {values|group-expression})

where values has the following syntax:

bottom-value [next-lowest-break-value] top-value

Arguments

expression

  The numeric expression whose values are to be categorized.

bottom-value

  A number that specifies the lowest number in the series and sets the bottom limit of category 1.

next-lowest-break-value

  A number that specifies the beginning of the range of the next category.

top-value

  A number that specifies the highest number in the series and sets the upper limit of the highest category.

group-expression

  A one-dimensional numeric expression that defines the break values for the categories.

Examples

Example 8-39 Specifying Category Range Values

Assume that your analytic workspace contains the following `geography` and `items` dimensions and `sales2` variable.

    DEFINE geography DIMENSION TEXT
    MAINTAIN geography ADD 'g1' 'g2' 'g3'
    DEFINE items DIMENSION TEXT
    MAINTAIN items ADD 'Item1' 'Item2' 'Item3' 'Item4' 'Item5'
    DEFINE sales2 DECIMAL <geography items>

Assume the `sales2` variable has the following data values.

    -------------SALES2-------------
                   -----------GEOGRAPHY------------
    ITEMS              g1         g2         g3
    -------------- ---------- ---------- ----------
    Item1               30.00      15.00      12.00
    Item2               10.00      20.00      18.00
    Item3               15.00      20.00      24.00
    Item4               30.00      25.00      25.00
    Item5                  NA       7.00      21.00

This statement reports the result of categorizing the `sales2` variable.

    REPORT CATEGORIZE(sales2 10 15 20 25)

The preceding statement produces the following output.

    -CATEGORIZE(SALES2 10 15 20 25)-
                   -----------GEOGRAPHY------------
    ITEMS              g1         g2         g3
    -------------- ---------- ---------- ----------
    Item1               -1.00       2.00       1.00
    Item2                1.00       3.00       2.00
    Item3                2.00       3.00       3.00
    Item4               -1.00       3.00       3.00
    Item5                  NA       0.00       3.00

Example 8-40 Specifying a Group-Expression

These statements define a `groups` dimension and a `groupval` variable.

    DEFINE groups DIMENSION TEXT
    MAINTAIN groups ADD 'Grp1' 'Grp2' 'Grp3' 'Grp4'
    DEFINE groupvals DECIMAL <groups>
    groupvals(groups 'Grp1') = 10
    groupvals(groups 'Grp2') = 15
    groupvals(groups 'Grp3') = 20
    groupvals(groups 'Grp4') = 25

This statement reports the result of calling the CATEGORIZE function with the `sales` variable as the expression argument and the `groupvals` variable as the group-expression argument of the call.

    REPORT CATEGORIZE(sales, groupvals)

The preceding statement produces the same output as the statement in the "Specifying Category Range Values" .